using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
namespace DataAccessLibrary
{
    ///(1)While Inserting into SQL Database Use [ convert(datetime,'21-02-2006',105)  ] for
    ///   Inserting dd-MM-yyy format date Input.
    ///(2)While Quering SQL Database Use [   convert(char(11),holidaydate,105)   ] for
    ///   fetching data in dd-MM-yyyy format ( remember when using it in where clause you have to use single codes to the date.
    ///These 2 method are very necessary and you don't have to think about which date format is active in database.
    /// <summary>
    /// Summary description for Sql.
    /// </summary>
    public class OleDataClient : IDisposable
    {

        private OleDbConnection Con;
        private OleDbCommand Cmd;
        private OleDbDataReader DR;
        private OleDbDataAdapter Adp;
        private OleDbTransaction Tran;
        private string ConnectionString;
        public OleDataClient()
        {
            if (ConfigurationManager.ConnectionStrings["ConnectionString"] == null)
            {
                throw new Exception("Connection string is not defined. Please check your database connection string.");
            }
            ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); ;
        }
        public void ConnectToDB()
        {
            try
            {
                if (Con == null) Con = new OleDbConnection(ConnectionString);
                if (Con.State != ConnectionState.Open) Con.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("Unble to connct to the Database" + ex.Message);
            }

        }
        private void CloseDB()
        {
            if (Con != null)
            {
                if (Con.State != ConnectionState.Closed) Con.Close();
                Con.Dispose();
                Con = null;
            }
        }

        public void StartTran()
        {
            ConnectToDB();
            Cmd = new OleDbCommand();
            Tran = Con.BeginTransaction(IsolationLevel.ReadCommitted);
            Cmd.Transaction = Tran;
        }
        public void RollBackTran()
        {
            Tran.Rollback();
            CloseDB();
        }
        public void CommitTran()
        {
            Tran.Commit();
            CloseDB();
        }
        public bool ExecuteNonQuery(string DML)
        {
            try
            {
                ConnectToDB();
                if (Cmd == null)
                {
                    Cmd = new OleDbCommand(DML);
                }
                else
                {
                    Cmd.CommandText = DML;
                }
                Cmd.CommandType = CommandType.Text;
                Cmd.Connection = Con;
                Cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetDataTable(string OleDb, string TableName)
        {
            DataTable DT = new DataTable();
            ConnectToDB();
            if (Cmd == null)
            {
                Cmd = new OleDbCommand(OleDb);
            }
            else
            {
                Cmd.CommandText = OleDb;
            }
            Cmd.CommandType = CommandType.Text;
            try
            {
                Cmd.Connection = Con;
                Adp = new OleDbDataAdapter(Cmd);
                Adp.Fill(DT);
                DT.TableName = TableName;
                Adp.Dispose();
                return DT.Copy();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public OleDbDataReader GetReadedDataRow(string OleDb)
        {
            ConnectToDB();
            if (Cmd == null)
            {
                Cmd = new OleDbCommand(OleDb);
            }
            else
            {
                Cmd.CommandText = OleDb;
            }
            Cmd.CommandType = CommandType.Text;
            try
            {
                Cmd.Connection = Con;
                DR = Cmd.ExecuteReader();
                return DR;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void executeProcedure(string procedureName)
        {
            ConnectToDB();
            if (Cmd == null)
            {
                Cmd = new OleDbCommand(procedureName);
            }
            else
            {
                Cmd.CommandText = procedureName;
            }
            Cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                Cmd.Connection = Con;
                Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public string GetValue(string SingleValueOleDb)
        {
            ConnectToDB();
            if (Cmd == null)
            {
                Cmd = new OleDbCommand(SingleValueOleDb);
            }
            else
            {
                Cmd.CommandText = SingleValueOleDb;
            }
            Cmd.CommandType = CommandType.Text;
            try
            {
                object str;
                Cmd.Connection = Con;
                str = Cmd.ExecuteScalar();
                if (str == null || str == DBNull.Value)
                {
                    return string.Empty;
                }
                else
                {
                    return str.ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public string GetServerDate()
        {
            string Str;
            Str = GetValue("Select convert(char(11),getdate()) ToDay ");
            Str = "'" + Str.Substring(0, 10) + "'";
            return Str;
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        ~OleDataClient()
        {
            Dispose(false);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                CloseDB();
                if (Con != null) Con.Dispose();
                if (Cmd != null) Cmd.Dispose();
                if (DR != null) DR.Dispose();
                if (Adp != null) Adp.Dispose();
                if (Tran != null) Tran.Dispose();
            }
        }

    }
}